﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace MvcApplication2.Models
{
    public static class MyDataProvider
    {
        private static Configuration rootWebConfig = WebConfigurationManager.OpenWebConfiguration("/MvcApplication2");
        private const string CONSTRINGNAME = "ApplicationServices";
        public static SqlConnection sqlConnection;

        public static SqlConnection Open()
        {
            ConnectionStringSettings conString = rootWebConfig.ConnectionStrings.ConnectionStrings[CONSTRINGNAME];
            sqlConnection = new SqlConnection(conString.ConnectionString);
            sqlConnection.Open();
            return sqlConnection;
        }

        public static DataTable excuteQuery(string storeName,SqlTransaction transaction=null)
        {
            var dt = new DataTable();
            var adap = new SqlDataAdapter(storeName, sqlConnection);
            adap.SelectCommand.CommandType = CommandType.StoredProcedure;
            if (transaction != null)
            {
                adap.SelectCommand.Transaction = transaction;
            }
            adap.Fill(dt);
            return dt;
        }

        public static object excuteScalar(string storeName)
        {
            var adap = new SqlDataAdapter(storeName, sqlConnection);
            adap.SelectCommand.CommandType = CommandType.StoredProcedure;
            return adap.SelectCommand.ExecuteScalar();
        }

        public static DataTable excuteQueryWithParameters(string storeName,List<SqlParameter> parameterList)
        {
            var dt = new DataTable();
            var adap = new SqlDataAdapter(storeName, sqlConnection);
            adap.SelectCommand.CommandType = CommandType.StoredProcedure;
            foreach (var parameter in parameterList)
            {
                adap.SelectCommand.Parameters.Add(parameter);
            }
            adap.Fill(dt);
            return dt;
        }

        public static object excuteScalarWithParameters(string storeName, List<SqlParameter> parameterList,SqlTransaction transaction=null)
        {
            var adap = new SqlDataAdapter(storeName, sqlConnection);
            adap.SelectCommand.CommandType = CommandType.StoredProcedure;
            if(transaction!=null)
            {
                adap.SelectCommand.Transaction = transaction;
            }
            foreach (var parameter in parameterList)
            {
                adap.SelectCommand.Parameters.Add(parameter);
            }
            return adap.SelectCommand.ExecuteScalar();
        }

        public static int insertQueryWithParameters(string storeName, List<SqlParameter> parameterList)
        {
            var adap = new SqlDataAdapter();
            adap.InsertCommand = new SqlCommand(storeName, sqlConnection);
            adap.InsertCommand.CommandType = CommandType.StoredProcedure;
            foreach (var parameter in parameterList)
            {
                adap.InsertCommand.Parameters.Add(parameter);
            }
            return adap.InsertCommand.ExecuteNonQuery();
        }

        public static int updateQueryWithParameters(string storeName, List<SqlParameter> parameterList,SqlTransaction transaction=null)
        {
            var adap = new SqlDataAdapter();
            adap.UpdateCommand = new SqlCommand(storeName, sqlConnection);
            adap.UpdateCommand.CommandType = CommandType.StoredProcedure;
            if (transaction != null)
            {
                adap.UpdateCommand.Transaction = transaction;
            }
            foreach (var parameter in parameterList)
            {
                adap.UpdateCommand.Parameters.Add(parameter);
            }
            return adap.UpdateCommand.ExecuteNonQuery();
        }

        public static int deleteQueryWithParameters(string storeName, List<SqlParameter> parameterList)
        {
            var adap = new SqlDataAdapter();
            adap.DeleteCommand = new SqlCommand(storeName, sqlConnection);
            adap.DeleteCommand.CommandType = CommandType.StoredProcedure;
            foreach (var parameter in parameterList)
            {
                adap.DeleteCommand.Parameters.Add(parameter);
            }
            return adap.DeleteCommand.ExecuteNonQuery();
        }

        public static void Close()
        {
            sqlConnection.Close();
        }
    }
}